import threading
import pymysql
import datetime
import socket

__author__ = "yangming, jiayi"


CURRENT_TIME = datetime.datetime(2016, 1, 18)
REPEAT_TIME = 0

ORIGINAL_TABLE = "app_urlcollect"  # "app_urlcollect_yang"
TMP_TABLE = "app_urlcollect_tmp"
TOTAL_TABLE = "app_urlcollect_total"
ANALYSIS_RESULT_TABLE = "app_url_analysis_result"


DB_OPTIONS = {
    "RUN": {
        "DB_HOST": '114.215.202.46',
        "DB_USER": 'root',
        "DB_PASS":  '@I#dian%Jia*',
        "DB_NAME": 'icloud',
        "DB_CHARSET": 'utf8'
    },
    "TEST": {
        "DB_HOST": 'localhost',
        "DB_USER": 'root',
        "DB_PASS":  'y@ngmin9',
        "DB_NAME": 'marketing',
        "DB_CHARSET": 'utf8'
    }
}

OPTION = "RUN"
if socket.gethostname() == "6U6ANY0FVN3KDEV":
    OPTION = "TEST"

DB = {
    "DB_HOST": DB_OPTIONS[OPTION]["DB_HOST"],
    "DB_USER": 'root',
    "DB_PASS":  'y@ngmin9',
    "DB_NAME": 'marketing',
    "DB_CHARSET": 'utf8'
}


DB_HOST = '114.215.202.46'
DB_USER = 'root'
DB_PASS = '@I#dian%Jia*'
DB_NAME = 'icloud'
DB_CHARSET = 'utf8'

# DB_HOST = '120.26.121.101'
# DB_USER = 'root'
# DB_PASS = '@GxDj#2015%'
# DB_NAME = 'icloud'
# DB_CHARSET = 'utf8'

# DB_HOST = 'localhost'
# DB_USER = 'root'
# DB_PASS = 'y@ngmin9'
# DB_NAME = 'icloud'
# DB_CHARSET = 'utf8'


class DBConnection:
    def __init__(self, db_host=DB_HOST,
                 db_user=DB_USER,
                 db_pass=DB_PASS,
                 db_name=DB_NAME,
                 db_charset=DB_CHARSET):

        self.DB_HOST = db_host
        self.DB_USER = db_user
        self.DB_PASS = db_pass
        self.DB_NAME = db_name
        self.DB_CHARSET = db_charset
        self.conn = pymysql.connect(host=self.DB_HOST,
                                    user=self.DB_USER,
                                    passwd=self.DB_PASS,
                                    db=self.DB_NAME,
                                    charset=self.DB_CHARSET)
        self.cur = self.conn.cursor()

    def execute(self, sql, args=None):
        self.cur.execute(sql, args=args)

    def fetchall(self):
        return self.cur.fetchall()

    def fetchone(self):
        return self.cur.fetchone()

    def row_count(self):
        return self.cur.rowcount

    def insert_id(self):
        return self.conn.insert_id()

    def executemany(self, sql, args=None):
        self.cur.executemany(sql, args=args)

    def close(self):
        return self.conn.close()

    def commit(self):
        return self.conn.commit()


class AnalysisDB(DBConnection):
    def __init__(self, db_host=DB_HOST,
                 db_user=DB_USER,
                 db_pass=DB_PASS,
                 db_name=DB_NAME,
                 db_charset=DB_CHARSET):

        super(self.__class__, self).\
            __init__(db_host=db_host,
                     db_user=db_user,
                     db_pass=db_pass,
                     db_name=db_name,
                     db_charset=db_charset)

    def get_all_router_mac(self):
        """
        从临时表中获取所有的router_mac
        :return:
        """
        self.execute("SELECT DISTINCT router_mac FROM `{}`;".format(TMP_TABLE))
        return self.fetchall()

    def get_urls_by_router_mac(self, router_mac):
        """
        根据获取到的router_mac查询每一条mac对应的url分组，
        并进行数量统计
        :param router_mac:
        :return:
        """
        # print("5. 根据获取到的router_mac查询每一条mac对应的url分组")
        if not router_mac:
            return None

        sql = "SELECT count(url.url) nums,url.client_mac, url.url, " \
              "url.report_time, url.router_mac, url.category," \
              "r.ssid, r.owner, r.router_groups_id," \
              " rg.name shopname, rg.address shopaddress, " \
              "rg.area_id, rg.city_id, rg.province_id " \
              "FROM `{}` AS url, app_routers " \
              "AS r, app_routergroups as rg " \
              "WHERE r.router_groups_id=rg.id AND url.router_mac=r.mac " \
              "AND r.mac='{}' GROUP BY url.url;"

        self.execute(sql.format(TMP_TABLE, router_mac))

        return self.fetchall()

    def migrate_url_2_result(self, datas):
        """
        将查询的数据插入到存储统计结果的总表中
        :return:
        """
        # print("4. 将查询的数据插入到存储统计结果的总表中")
        if not datas:
            return

        values = [data for data in datas]

        if datas:
            sql = "INSERT INTO `{}`(" \
              "`nums` , `client_mac`, `url`, `report_time`, `router_mac`, `category`, `ssid`, `owner`, " \
              "`router_groups_id`, `shopname`, `shopaddress`, `area_id`, `city_id`, `province_id`) VALUES (" \
              "%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);".format(ANALYSIS_RESULT_TABLE)
            self.executemany(sql, values)
            # self.commit()

    def migrate_from_tmp(self):
        """
        从临时表中关联查询出url，并将符合条件的url的所需要的字段放入到result表中
        :return:
        """
        # print("3. 从临时表中关联查询出url，并将符合条件的url的所需要的字段放入到result表中")

        macs = self.get_all_router_mac()

        for mac in macs:
            if mac and mac[0]:
                urls = self.get_urls_by_router_mac(mac[0].strip())
                self.migrate_url_2_result(urls)

        # 将临时表中的数据迁移完成后，清空临时表
        self.execute("TRUNCATE TABLE `{}`;".format(TMP_TABLE))
        self.commit()

    def get_url_from_original(self, start_time, end_time):
        """
        从原始表中读取指定天时间中的数据
        :param start_time:
        :param end_time:
        :return:
        """
        # print("1. 从原始表中获取指定日期的数据")

        sql = "SELECT `id`, `client_mac`, `url`, `report_time`, `router_mac`, `category` " \
              "FROM `{}` WHERE report_time BETWEEN '{}' AND '{}'".format(ORIGINAL_TABLE,
                                                                         start_time, end_time)

        self.execute(sql)
        return self.fetchall()

    def migrate_original_url(self, results):
        # print("2.  将原始表中的数据迁移到总表或临时表:")
        """
        从原始表中获取指定日期的数据
        :param results:
        :param table:
        :return:
        """
        if not results:
            return

        values = [(data[1], data[2], data[3], data[4], data[5] if data[5] else "") for data in results]

        if results:
            # print(values)
            sql = "INSERT INTO `{}` (`client_mac` ,`url` ,`report_time` ,`router_mac` ,`category`) " \
                  "VALUES (%s,%s,%s,%s,%s)"
            # print(sql)

            # 1. 向临时表中插入数据
            # print("2.1  向临时表中插入数据")
            self.executemany(sql.format(TMP_TABLE), values)
            # self.commit()

            # # 2. 向总表中插入数据
            # print("2.2  向总表中插入数据")
            # self.executemany(sql.format(TOTAL_TABLE), values)
            # self.commit()

            # 4. 将临时表中的数据按条件查询，并加入到总表中
            # print("2.4  将临时表中的数据按条件查询，并加入到总表中")
            self.migrate_from_tmp()

            # 3. 将原始表中的数据清除
            # print("2.3  将原始表中的数据清除")
            values = [(data[0],) for data in results]
            # print(values)
            sql = "DELETE FROM `{}` WHERE id=%s;".format(ORIGINAL_TABLE)
            self.executemany(sql, values)
            self.commit()
